﻿//===============================================================================
// 编写：楚旋
// 博客：http://eflylab.cnblogs.com
// 时间：2008-04-17
//==============================================================================
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
public partial class _Default : System.Web.UI.Page 
{
    
    protected void Page_Load(object sender, EventArgs e)
    {
       
    }

    private void BindData(DataTable dt)
    {
        repData.DataSource = dt;
        repData.DataBind();
    }
    protected void btnLoadDt_Click(object sender, EventArgs e)
    {
        BindData(LoadDataTable());
    }

    //创建DataTable
    private DataTable LoadDataTable()
    {
        DataTable dt = new DataTable();
        DataColumn dc = new DataColumn("userName");
        dt.Columns.Add(dc);
        dc = new DataColumn("userSex");
        dt.Columns.Add(dc);

        DataRow dr = dt.NewRow();
        dr[0] = "fengyan";
        dr[1] = "male";
        dt.Rows.Add(dr);

        dr = dt.NewRow();
        dr[0] = "efly";
        dr[1] = "male";
        dt.Rows.Add(dr);

        dr = dt.NewRow();
        dr[0] = "楚旋";
        dr[1] = "male";
        dt.Rows.Add(dr);
        
        dr = dt.NewRow();
        dr[0] = "51aspx";
        dr[1] = "male";
        dt.Rows.Add(dr);

        return dt;
    }

    protected void btnExportExcel_Click(object sender, EventArgs e)
    {
        //得到需要导入Excel的DataTable
        DataTable dt = LoadDataTable();
        //将其列名添加进去！ （这一步注意是为了方便以后将该Excel导入内存表中 自动创建列名用。）
        //DataRow dr = dt.NewRow();
        //dr[0] = "userName";
        //dr[1] = "userSex";
        //dt.Rows.InsertAt(dr, 0);

        //实例化一个Excel助手工具类
        ExcelHelper ex = new ExcelHelper();
        //导入所有！（从第一行第一列开始）
        ex.DataTableToExcel(dt, 5, 1);
        //导出Excel保存的路径！
        ex.OutputFilePath = txtExcelPath.Text;
        ex.OutputExcelFile();        
        
    }
  
    protected void btnExcelToDataTable_Click(object sender, EventArgs e)
    {
        string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtFromExcel.Text + ";Extended Properties=Excel 8.0";
        //链接Excel
        OleDbConnection cnnxls = new OleDbConnection(strConn);
        //读取Excel里面有 表Sheet1
        OleDbDataAdapter oda = new OleDbDataAdapter("select * from [Sheet1$]", cnnxls);
        DataSet ds = new DataSet();
        //将Excel里面有表内容装载到内存表中！
        oda.Fill(ds);
        DataTable dt = ds.Tables[0];
        BindData(dt);
    }
}
